<?php
// Single line prepared PDO statement creation/execution.
// Part of mmvc library, https://code.google.com/p/mmvc/
// Dependencies: None

/** 
 * Prepared PDO statement execution using specified params.
 * Suitable for delete/update type of statements which don't need result set, see sqlQuery for result-set returning function.
 * @param $db PDO
 * @param $q SQL-query
 * @param $p Params
 * @return Prepared statement. 
 */
function sqlExec( &$db, $q, $p=array() ) {
	$s = $db->prepare( $q );
	$s->execute( $p );
	return $s;
}

/** 
 * Prepared PDO SQL INSERT statement execution using specified params.
 * @param $db PDO
 * @param $q SQL-query
 * @param $p Params
 * @return Insert id
 */
function sqlInsert( &$db, $q, $p=array() ) {
	$s = $db->prepare( $q );
	$s->execute( $p );
	return $db->lastInsertId();
}

/**
 * Executes SQL INSERT for all key-value pairs to specified table.
 */
function sqlInsertRow( &$db, $tab, $o ) {
	$keys = array_keys($o);
	$sql = "insert into $tab (".implode(',',$keys).") values (:".implode(',:',$keys).")";
	return sqlInsert( $sql, $o );
}

/** 
 * Query DB rows using specified prepared statement and params.
 * Suitable for query type of statements which need result set, see sqlExec for update/insert type of statements.
 * @param $db PDO
 * @param $q SQL-query
 * @param $p Params
 * @param $flags Fetch flags. Default is PDO::FETCH_ASSOC.
 * @return DB rows result set
 */
function sqlQuery( &$db, $q, $p=array(), $flags=PDO::FETCH_ASSOC ) {
	$s = $db->prepare( $q );
	$s->execute( $p );
	return $s->fetchAll( $flags );
}

/** 
 * Query DB columns using specified prepared statement and params.
 * Returns result column as array:
 * If the result set has one column, plain index based array is returned.
 * If the result set has two or more columns, map is formed by using the first column as key and second as value.
 * @param $db PDO
 * @param $q SQL-query
 * @param $p Params
 * @return See above
 */
function sqlQueryCol( &$db, $q, $p=array() ) {
	$s = $db->prepare( $q );
	$s->execute( $p );
	$res = $s->fetchAll( PDO::FETCH_NUM );
	if ( empty($res) || sizeof($res[0]) == 0 )
		return array();
	$a = array();
	foreach ($res as $r)
	{
		if ( sizeof($r) == 1 )
			$a[] = $r[0];
		else
			$a[ $r[0] ] = $r[1];
	}
	return $a;
}

/** 
 * Prepared statement single-row query using specified params.
 * Suitable for query type of statements which need single item from result set, 
 * see sqlExec for update/insert type of statements and sqlQuery for result sets which contain multiple rows.
 * @param $db PDO
 * @param $q SQL-query
 * @param $p Params
 * @param $flags Fetch flags. Default is PDO::FETCH_ASSOC.
 * @return The first row of result rows or false if none.
 */
function sqlQueryOne( &$db, $q, $p=array(), $flags=PDO::FETCH_ASSOC ) {
	$stm = $db->prepare($q);
	$stm->execute($p);
	$row = $stm->fetch($flags);
	$stm->closeCursor();
	return $row;
}

/** 
 * Query one column of the first row matching specified search in DB.
 * @param $q SQL query string. Use ? as replacement for parameters
 * @param $db PDO
 * @param $q SQL-query
 * @param $p Array of parameters.
 * @param $i Column index
 */ 
function sqlQueryOneCol( &$db, $q, $p=array(), $i=0 ) {
	$stm = $db->prepare($q);
	$stm->execute($p);
	$v = $stm->fetchColumn($i);
	$stm->closeCursor();
	return $v;
}

?>